const db = require("../config/mysql");

class DailyLogModel {
  constructor() {
    this.db = db;
    this.mainTable = "daily_logs";
    this.eatTimeMap = {
      0: "全天",
      1: "早餐",
      2: "中餐",
      3: "晚餐",
    };
  }

  // 检查是否存在相同 log_date 和 eat_time 的记录
  async checkLogExists(log_date, eat_time, excludeId = null) {
    let sql = `SELECT id FROM ${this.mainTable} WHERE log_date = ? AND eat_time = ?`;
    const params = [log_date, Number(eat_time)];

    if (excludeId !== null) {
      sql += ` AND id != ?`;
      params.push(excludeId);
    }
    sql += ` LIMIT 1`;

    const [result] = await this.db.query(sql, params);
    return result.length > 0;
  }

  // 根据日期计算当日的食材总成本 (food_cost) 和 包间食材成本 (room_food_cost)
  async calculateDailyFoodCost(log_date) {
    if (!log_date || !/^\d{4}-\d{2}-\d{2}$/.test(log_date)) {
      throw new Error("计算成本时，日期格式错误");
    }
    const sql = `
			SELECT
				-- 非包间成本 (stall_id 1 和 2)
				SUM(CASE WHEN i.stall_id IN (1, 2) THEN i.total_amount ELSE 0 END) AS food_cost_non_room,
				-- 包间成本 (stall_id 3)
				SUM(CASE WHEN i.stall_id = 3 THEN i.total_amount ELSE 0 END) AS food_cost_room
			FROM inventoryout i
			WHERE DATE(i.out_time) = ?
			AND i.status = 2
		`;

    const [result] = await this.db.query(sql, [log_date]);
    const stats = result[0];
    return {
      food_cost: Number(stats.food_cost_non_room || 0).toFixed(3),
      room_food_cost: Number(stats.food_cost_room || 0).toFixed(3),
    };
  }

  // 获取日志列表（筛选、分页）
  async getLogList(query = {}) {
    let {
      startDate,
      endDate,
      log_week,
      eat_time,
      page = 1,
      pageSize = 10,
    } = query;
    const offset = (page - 1) * pageSize;

    // 查询条件
    let conditions = [];
    let params = [];

    if (startDate) {
      conditions.push("log_date >= ?");
      params.push(startDate);
    }
    if (endDate) {
      conditions.push("log_date <= ?");
      params.push(endDate);
    }
    if (log_week) {
      conditions.push("log_week = ?");
      params.push(log_week);
    }
    // 就餐时段筛选
    if (eat_time !== undefined && eat_time !== null) {
      const validEatTimes = Object.keys(this.eatTimeMap).map(Number);
      if (validEatTimes.includes(Number(eat_time))) {
        conditions.push("eat_time = ?");
        params.push(Number(eat_time));
      }
    }

    const whereClause = conditions.length
      ? `WHERE ${conditions.join(" AND ")}`
      : "";

    // 查询总数
    const countSql = `SELECT COUNT(*) AS total FROM ${this.mainTable} ${whereClause}`;
    const [countResult] = await this.db.query(countSql, [...params]);
    const total = Number(countResult[0].total);

    // 查询列表
    const listSql = `
			SELECT * FROM ${this.mainTable}
			${whereClause}
			ORDER BY log_date DESC
			LIMIT ?, ?
		`;
    params.push(offset, Number(pageSize));
    const [list] = await this.db.query(listSql, params);

    return {
      list,
      pagination: {
        total,
        page: Number(page),
        pageSize: Number(pageSize),
        totalPages: Math.ceil(total / pageSize),
      },
    };
  }

  // 根据ID获取详情
  async getLogById(id) {
    const sql = `SELECT * FROM ${this.mainTable} WHERE id = ?`;
    const [result] = await this.db.query(sql, [id]);
    const log = result[0] || null;
    if (log && log.log_date) {
      if (log.log_date instanceof Date) {
        const date = log.log_date;
        const year = date.getFullYear();
        const month = String(date.getMonth() + 1).padStart(2, "0");
        const day = String(date.getDate()).padStart(2, "0");
        log.log_date = `${year}-${month}-${day}`;
      } else if (typeof log.log_date === "string") {
        log.log_date = log.log_date.split("T")[0];
      }
    }
    return log;
  }

  // 验证
  validateData(data) {
    const {
      log_date,
      meal_count,
      meal_sales,
      qcode_count,
      qcode_sales,
      card_count,
      card_sales,
      ticket_count,
      ticket_sales,
      room_food_sales,
      room_food_ratio,
      eat_time,
      remark,
    } = data;

    if (!log_date) {
      throw new Error("记账日期（log_date）为必填项");
    }

    if (eat_time === undefined || eat_time === null) {
      throw new Error("就餐时间（eat_time）为必填项");
    }

    // 日期校验
    const dateRegex = /^\d{4}-\d{2}-\d{2}$/;
    if (!dateRegex.test(log_date)) {
      throw new Error("记账日期格式错误，应为YYYY-MM-DD");
    }

    // 就餐时段校验（必须是 0/1/2/3）
    const validEatTimes = Object.keys(this.eatTimeMap).map(Number);
    if (!validEatTimes.includes(Number(eat_time))) {
      const timeDescs = validEatTimes
        .map((val) => `${val}（${this.eatTimeMap[val]}）`)
        .join("、");
      throw new Error(`就餐时间无效，允许值：${timeDescs}`);
    }

    // 数字校验
    const integerFields = [
      "meal_count",
      "qcode_count",
      "card_count",
      "ticket_count",
      "eat_time",
    ];
    integerFields.forEach((field) => {
      const value = data[field];
      if (value !== undefined && value !== null) {
        if (!Number.isInteger(Number(value)) || Number(value) < 0) {
          throw new Error(`${this.getFieldDesc(field)}必须是非负整数`);
        }
      }
    });

    // 金额校验（非负数，保留2位小数）
    const amountFields = [
      "meal_sales",
      "qcode_sales",
      "card_sales",
      "ticket_sales",
    ];
    amountFields.forEach((field) => {
      const value = data[field];
      if (value !== undefined && value !== null) {
        const num = Number(value);
        if (isNaN(num) || num < 0 || !Number.isFinite(num)) {
          throw new Error(`${this.getFieldDesc(field)}必须是非负数`);
        }
        if (num.toString().split(".")[1]?.length > 2) {
          throw new Error(`${this.getFieldDesc(field)}最多保留2位小数`);
        }
      }
    });

    // 占比类型校验
    const ratioFields = ["room_food_ratio"];
    ratioFields.forEach((field) => {
      const value = data[field];
      if (value !== undefined && value !== null) {
        const num = Number(value);
        if (isNaN(num) || num < 0 || num > 1) {
          throw new Error(
            `${this.getFieldDesc(field)}必须是0到1之间的数字（如 0.500）`
          );
        }
        // 校验小数位数（最多3位）
        if (num.toString().split(".")[1]?.length > 3) {
          throw new Error(`${this.getFieldDesc(field)}最多保留3位小数`);
        }
      }
    });

    // 关联校验（数量有值时，金额必传）
    if (meal_count && (meal_sales === undefined || meal_sales === null)) {
      throw new Error("包餐总数存在时，包餐预估营业额不能为空");
    }
    if (qcode_count && (qcode_sales === undefined || qcode_sales === null)) {
      throw new Error("二维码用餐数存在时，二维码营业额不能为空");
    }
    if (card_count && (card_sales === undefined || card_sales === null)) {
      throw new Error("卡机用餐数存在时，卡机营业额不能为空");
    }
    if (ticket_count && (ticket_sales === undefined || ticket_sales === null)) {
      throw new Error("餐票挂账数存在时，餐票挂账金额不能为空");
    }
  }

  // 字段描述
  getFieldDesc(field) {
    const descMap = {
      meal_count: "包餐总数",
      qcode_count: "二维码用餐数",
      card_count: "卡机用餐数",
      ticket_count: "餐票挂账数",
      meal_sales: "包餐预估营业额",
      qcode_sales: "二维码用餐营业额",
      card_sales: "卡机用餐营业额",
      ticket_sales: "餐票挂账金额",
      food_cost: "食材总成本",
      room_food_cost: "包间食材成本",
      room_food_ratio: "包间食材占比",
      eat_time: "就餐时间",
    };
    return descMap[field] || field;
  }

  // 自动计算总营业额、成本占比及包间单独数据
  calculateDerivedFields(data) {
    const {
      meal_sales = 0,
      qcode_sales = 0,
      card_sales = 0,
      ticket_sales = 0,
      room_food_sales = 0,
      food_cost,
      room_food_cost,
      eat_time,
    } = data;

    // 转换为数字
    const num_meal_sales = Number(meal_sales);
    const num_qcode_sales = Number(qcode_sales);
    const num_card_sales = Number(card_sales);
    const num_ticket_sales = Number(ticket_sales);
    const num_room_food_sales = Number(room_food_sales);

    // 营业额：非包间、包间
    const non_room_sales =
      num_meal_sales + num_qcode_sales + num_card_sales + num_ticket_sales;
    const room_sales = num_room_food_sales;

    // 总营业额
    const total_sales = non_room_sales;

    // 成本
    const finalFoodCost = Number(food_cost);
    const finalRoomFoodCost = Number(room_food_cost);
    const non_room_cost = finalFoodCost - finalRoomFoodCost;
    const finalNonRoomCost = Math.max(0, non_room_cost);

    // 占比
    let food_cost_ratio = 0;
    let room_food_ratio = 0;

    if (total_sales > 0) {
      food_cost_ratio = Number((finalFoodCost / total_sales).toFixed(3));
    }

    if (room_sales > 0 && finalRoomFoodCost >= 0) {
      room_food_ratio = Number((finalRoomFoodCost / room_sales).toFixed(3));
    }

    // 星期
    const log_week = [
      "星期日",
      "星期一",
      "星期二",
      "星期三",
      "星期四",
      "星期五",
      "星期六",
    ][new Date(data.log_date).getDay()];

    return {
      ...data,
      log_week,
      eat_time: eat_time !== undefined ? Number(eat_time) : null,
      non_room_sales: non_room_sales.toFixed(2),
      room_sales: room_sales.toFixed(2),
      total_sales: total_sales.toFixed(2),

      meal_sales: num_meal_sales.toFixed(2),
      qcode_sales: num_qcode_sales.toFixed(2),
      card_sales: num_card_sales.toFixed(2),
      ticket_sales: num_ticket_sales.toFixed(2),
      room_food_sales: num_room_food_sales.toFixed(2),

      food_cost: finalFoodCost.toFixed(2),
      room_food_cost: finalRoomFoodCost.toFixed(2),

      food_cost_ratio,
      room_food_ratio,
    };
  }

  // 检查日期是否已存在
  async checkDateExists(log_date, eat_time) {
    return this.checkLogExists(log_date, eat_time, null);
  }

  // 添加
  async createLog(data) {
    try {
      // 验证
      this.validateData(data);

      // 自动计算当天的食材成本
      const calculatedCosts = await this.calculateDailyFoodCost(data.log_date);
      data.food_cost = calculatedCosts.food_cost;
      data.room_food_cost = calculatedCosts.room_food_cost;

      const finalData = this.calculateDerivedFields(data);

      // 插入数据库
      const {
        log_date,
        log_week,
        meal_count,
        meal_sales,
        qcode_count,
        qcode_sales,
        card_count,
        card_sales,
        ticket_count,
        ticket_sales,
        food_cost,
        total_sales,
        food_cost_ratio,
        room_food_cost,
        room_food_sales,
        room_food_ratio,
        eat_time,
        remark,
      } = finalData;

      const sql = `
				INSERT INTO ${this.mainTable} (
					log_date, log_week, meal_count, meal_sales, qcode_count, 
					qcode_sales, card_count, card_sales, ticket_count, ticket_sales,
					food_cost, total_sales, food_cost_ratio, room_food_cost, 
					room_food_sales, room_food_ratio, eat_time, remark
				) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
			`;

      const [result] = await this.db.query(sql, [
        log_date,
        log_week,
        meal_count || null,
        meal_sales || null,
        qcode_count || null,
        qcode_sales || null,
        card_count || null,
        card_sales || null,
        ticket_count || null,
        ticket_sales || null,
        food_cost,
        total_sales,
        food_cost_ratio,
        room_food_cost || null,
        room_food_sales || null,
        room_food_ratio || null,
        eat_time,
        remark || null,
      ]);

      return { id: result.insertId };
    } catch (error) {
      throw error;
    }
  }

  // 更新
  async updateLog(id, data) {
    try {
      this.validateData(data);

      const originalLog = await this.getLogById(id);
      if (!originalLog) {
        throw new Error("当前记录不存在，无法更新");
      }
      const newLogDate = data.log_date || originalLog.log_date;
      const newEatTime =
        data.eat_time !== undefined
          ? Number(data.eat_time)
          : originalLog.eat_time;

      // 自动计算当天的食材成本
      const calculatedCosts = await this.calculateDailyFoodCost(newLogDate);

      const updateData = {
        ...originalLog,
        ...data,
        log_date: newLogDate,
        eat_time: newEatTime,
        food_cost: calculatedCosts.food_cost,
        room_food_cost: calculatedCosts.room_food_cost,
      };
      const finalData = this.calculateDerivedFields(updateData);

      const {
        log_date,
        log_week,
        meal_count,
        meal_sales,
        qcode_count,
        qcode_sales,
        card_count,
        card_sales,
        ticket_count,
        ticket_sales,
        food_cost,
        total_sales,
        food_cost_ratio,
        room_food_cost,
        room_food_sales,
        room_food_ratio,
        eat_time,
        remark,
      } = finalData;

      const sql = `
				UPDATE ${this.mainTable} SET
					log_date = ?, log_week = ?, meal_count = ?, meal_sales = ?, qcode_count = ?,
					qcode_sales = ?, card_count = ?, card_sales = ?, ticket_count = ?, ticket_sales = ?,
					food_cost = ?, total_sales = ?, food_cost_ratio = ?, room_food_cost = ?,
					room_food_sales = ?, room_food_ratio = ?, eat_time = ?, remark = ?
				WHERE id = ?
			`;

      const [result] = await this.db.query(sql, [
        log_date,
        log_week,
        meal_count || null,
        meal_sales || null,
        qcode_count || null,
        qcode_sales || null,
        card_count || null,
        card_sales || null,
        ticket_count || null,
        ticket_sales || null,
        food_cost,
        total_sales,
        food_cost_ratio,
        room_food_cost || null,
        room_food_sales || null,
        room_food_ratio || null,
        eat_time,
        remark || null,
        id,
      ]);

      return { affectedRows: result.affectedRows };
    } catch (error) {
      throw error;
    }
  }

  // 删除
  async deleteLog(id) {
    const sql = `DELETE FROM ${this.mainTable} WHERE id = ?`;
    const [result] = await this.db.query(sql, [id]);
    return { affectedRows: result.affectedRows };
  }

  // 统计
  async getCoreStatistics(query = {}) {
    const { startDate, endDate, eat_time, log_week } = query;

    let conditions = [];
    let params = [];

    if (startDate) {
      conditions.push("log_date >= ?");
      params.push(startDate);
    }
    if (endDate) {
      conditions.push("log_date <= ?");
      params.push(endDate);
    }
    if (eat_time !== undefined && eat_time !== null) {
      const validEatTimes = Object.keys(this.eatTimeMap).map(Number);
      if (validEatTimes.includes(Number(eat_time))) {
        conditions.push("eat_time = ?");
        params.push(Number(eat_time));
      }
    }
    if (log_week) {
      const validWeeks = [
        "星期一",
        "星期二",
        "星期三",
        "星期四",
        "星期五",
        "星期六",
        "星期日",
      ];
      if (validWeeks.includes(log_week)) {
        conditions.push("log_week = ?");
        params.push(log_week);
      }
    }

    const whereClause = conditions.length
      ? `WHERE ${conditions.join(" AND ")}`
      : "";

    const sql = `
      SELECT
        SUM(IFNULL(meal_count, 0) + IFNULL(qcode_count, 0) + IFNULL(card_count, 0) + IFNULL(ticket_count, 0)) AS total_diner_count,
        SUM(IFNULL(meal_sales, 0) + IFNULL(qcode_sales, 0) + IFNULL(card_sales, 0) + IFNULL(ticket_sales, 0)) AS total_non_room_sales,
        SUM(IFNULL(ticket_sales, 0)) AS total_ticket_sales,
        SUM(IFNULL(food_cost, 0)) AS total_non_room_food_cost,
        SUM(IFNULL(room_food_sales, 0)) AS total_room_sales,
        SUM(IFNULL(room_food_cost, 0)) AS total_room_food_cost
      FROM ${this.mainTable}
      ${whereClause}
    `;

    const [result] = await this.db.query(sql, params);
    const stats = result[0];

    // 获取
    const totalDinerCount = Number(stats.total_diner_count) || 0;
    const totalNonRoomSales = Number(stats.total_non_room_sales) || 0;
    const totalNonRoomFoodCost = Number(stats.total_non_room_food_cost) || 0;
    const totalRoomSales = Number(stats.total_room_sales) || 0;
    const totalRoomFoodCost = Number(stats.total_room_food_cost) || 0;
    const totalTicketSales = Number(stats.total_ticket_sales) || 0;

    // 筛选总记录条数
    const countSql = `SELECT COUNT(*) AS total_log_count FROM ${this.mainTable} ${whereClause}`;
    const [countResult] = await this.db.query(countSql, [...params]);
    const totalLogCount = Number(countResult[0].total_log_count);

    // 计算指标
    // 人均客单价（基于非包间收入和总人数计算）
    const avgPricePerDiner =
      totalDinerCount > 0
        ? Number((totalNonRoomSales / totalDinerCount).toFixed(2))
        : 0;

    // 食堂成本占比 (非包间成本 / 非包间收入)
    const nonRoomCostRatio =
      totalNonRoomSales > 0
        ? Number((totalNonRoomFoodCost / totalNonRoomSales).toFixed(3))
        : 0;

    // 包间成本占比 (包间成本 / 包间收入)
    const roomCostRatio =
      totalRoomSales > 0
        ? Number((totalRoomFoodCost / totalRoomSales).toFixed(3))
        : 0;

    return {
      total_diner_count: totalDinerCount, // 就餐人次
      total_non_room_sales: Number(totalNonRoomSales.toFixed(2)), // 食堂总营业额
      avg_price_per_diner: avgPricePerDiner, // 人均客单价
      total_non_room_food_cost: Number(totalNonRoomFoodCost.toFixed(2)), // 食材成本汇总
      non_room_cost_ratio: nonRoomCostRatio, // 食堂食材成本占比
      total_ticket_sales: Number(totalTicketSales.toFixed(2)), // 挂账总额

      total_room_sales: Number(totalRoomSales.toFixed(2)), // 包间营业额
      total_room_food_cost: Number(totalRoomFoodCost.toFixed(2)), // 包间食材成本
      room_cost_ratio: roomCostRatio, // 包间食材成本占比

      total_log_count: totalLogCount, // 记录条数
    };
  }
}

module.exports = DailyLogModel;
